create view vPayments as
     select max(C.TRANS_NUMBER)                     TRANS_NUMBER,
           max(C.TRANSACTION_DATE)                TRANSACTION_DATE,
           max(C.JOURNAL_TYPE)                         JOURNAL_TYPE,
           max(C.TRANSACTION_TYPE)                 TRANSACTION_TYPE,
           sum(C.AMOUNT)                                     AMOUNT,
           max(C.BT_ID)                                         BT_ID,
           max(C.DESCRIPTION)                            DESCRIPTION,
           max(C.OWNER_ORG_CODE)                 OWNER_ORG_CODE,
           max(Cash_Accounts.ACCOUNT_TYPE)  ACCOUNT_TYPE,
           max(C.INVOICE_REFERENCE_NUM)      INVOICE_REFERENCE_NUM,
           max(Activity.SOURCE_CODE)                SOURCE_CODE,
           max(C.ACTION_CODES)                        ACTION_CODES,
           max(C.PRODUCT_CODE)                       PRODUCT_CODE,
           max(P.SOLICITOR_ID)                          SOLICITOR_ID,
           max(C.CHECK_NUMBER)                       CHECK_NUMBER,
           max(Activity.CAMPAIGN_CODE)           CAMPAIGN_CODE,
           max(C.INSTALL_BILL_DATE)                 INSTALL_BILL_DATE,
           sum(C.INVOICE_CREDITS)                    INVOICE_CREDITS,
           max(Activity.SEQN)                               SEQN
     from Trans as P
     INNER JOIN Invoice on Invoice.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER and Invoice.SOURCE_SYSTEM='FR'
     INNER JOIN Trans as C on C.INVOICE_REFERENCE_NUM=Invoice.REFERENCE_NUM
      and ((C.JOURNAL_TYPE = 'PAY' and C.TRANSACTION_TYPE = 'AR')    
      or  (C.JOURNAL_TYPE = 'IN' and C.TRANSACTION_TYPE = 'PAY'))
     LEFT OUTER JOIN Cash_Accounts on C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
     LEFT OUTER JOIN Activity on P.ACTIVITY_SEQN = Activity.SEQN
     and P.LINE_NUMBER = 1 and  P.SUB_LINE_NUMBER = 1
     and P.JOURNAL_TYPE = 'IN' and P.TRANSACTION_TYPE = 'DIST'    
group by C.TRANS_NUMBER,C.INVOICE_REFERENCE_NUM

GO
GRANT REFERENCES ON  [dbo].[vPayments] TO [IMIS]
GRANT SELECT ON  [dbo].[vPayments] TO [IMIS]
GRANT INSERT ON  [dbo].[vPayments] TO [IMIS]
GRANT DELETE ON  [dbo].[vPayments] TO [IMIS]
GRANT UPDATE ON  [dbo].[vPayments] TO [IMIS]
GO